package com.esm.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.esm.dao.SelectRecordDao_zxk;
import com.esm.model.Attend;
import com.esm.utils.JDBCUtil;
/**
 * 查询考勤记录 Dao实现
 * @author 康少
 *
 * 2017年1月4日
 */
public class SelectRecordDaoImpl_zxk implements SelectRecordDao_zxk{

	public int getOvertimeCount(String dep_name,int year,int month) {
		// TODO Auto-generated method stub
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(ems_attend.uid) ucount");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '加班'");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				flag = rs.getInt("ucount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return flag;
	}


	public List<Attend> getOvertimeAttend(String dep_name,int year,int month,int page,int rows) {
		// TODO Auto-generated method stub
		List<Attend> attends = new ArrayList<Attend>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ems_attend.att_id,ems_attend.uid,ems_attend.uname,ems_attend.state_zxk,");
		sql.append("ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '加班'");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			pst.setInt(4, (page-1)*rows);
			pst.setInt(5, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Attend attend = new Attend();
				attend.setAtt_id(rs.getInt("att_id"));
				attend.setUid(rs.getInt("uid"));
				attend.setUname(rs.getString("uname"));
				attend.setState(rs.getString("state_zxk"));
				attend.setTime(rs.getString("time"));
				attend.setDate(rs.getString("date"));
				attend.setRemark(rs.getString("remark"));
				attends.add(attend);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return attends;
	}


	public int getBtravelCount(String dep_name,int year,int month) {
		// TODO Auto-generated method stub
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(ems_attend.uid) ucount");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '出差'");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				flag = rs.getInt("ucount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return flag;
	}


	public List<Attend> getBtravelAttend(String dep_name,int year,int month,int page,int rows) {
		// TODO Auto-generated method stub
		List<Attend> attends = new ArrayList<Attend>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ems_attend.att_id,ems_attend.uid,ems_attend.uname,ems_attend.state_zxk,");
		sql.append("ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '出差'");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			pst.setInt(4, (page-1)*rows);
			pst.setInt(5, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Attend attend = new Attend();
				attend.setAtt_id(rs.getInt("att_id"));
				attend.setUid(rs.getInt("uid"));
				attend.setUname(rs.getString("uname"));
				attend.setState(rs.getString("state_zxk"));
				attend.setTime(rs.getString("time"));
				attend.setDate(rs.getString("date"));
				attend.setRemark(rs.getString("remark"));
				attends.add(attend);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return attends;
	}


	public int getLeavelCount(String dep_name,int year,int month) {
		// TODO Auto-generated method stub
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(ems_attend.uid) ucount");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '请假'");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				flag = rs.getInt("ucount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return flag;
	}


	public List<Attend> getLeavelAttend(String dep_name,int year,int month,int page,int rows) {
		// TODO Auto-generated method stub
		List<Attend> attends = new ArrayList<Attend>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ems_attend.att_id,ems_attend.uid,ems_attend.uname,ems_attend.state_zxk,");
		sql.append("ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '请假'");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			pst.setInt(4, (page-1)*rows);
			pst.setInt(5, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Attend attend = new Attend();
				attend.setAtt_id(rs.getInt("att_id"));
				attend.setUid(rs.getInt("uid"));
				attend.setUname(rs.getString("uname"));
				attend.setState(rs.getString("state_zxk"));
				attend.setTime(rs.getString("time"));
				attend.setDate(rs.getString("date"));
				attend.setRemark(rs.getString("remark"));
				attends.add(attend);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return attends;
	}

	/**
	 * 获得条件查询加班记录总数
	 */
	public int getOvertimeCountByTerm(String dep_name, int year, int month) {
		// TODO Auto-generated method stub
		int a = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(attend.uid) count");
		sql.append(" FROM (");
		sql.append(" SELECT ems_attend.uid,ems_attend.uname,");
		sql.append(" ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '加班') AS attend");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				a = rs.getInt("count");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return a;
	}
	/**
	 * 分页条件查询加班记录
	 */
	public List<Attend> getOvertimeAttendByTerm(String dep_name, int year, int month, int page, int rows) {
		// TODO Auto-generated method stub
		List<Attend> attends = new ArrayList<Attend>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ems_attend.uid,ems_attend.uname,");
		sql.append("ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '加班'");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			pst.setInt(4, (page-1)*rows);
			pst.setInt(5, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Attend attend = new Attend();
				attend.setUid(rs.getInt("uid"));
				attend.setUname(rs.getString("uname"));
				attend.setTime(rs.getString("time"));
				attend.setDate(rs.getString("date"));
				attend.setRemark(rs.getString("remark"));
				attends.add(attend);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return attends;
	}

	/**
	 * 获得条件查询请假记录总数
	 */
	public int getLeavelCountByTerm(String dep_name, int year, int month) {
		// TODO Auto-generated method stub
		int a = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(attend.uid) count");
		sql.append(" FROM (");
		sql.append(" SELECT ems_attend.uid,ems_attend.uname,");
		sql.append("ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '请假') AS attend");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				a = rs.getInt("count");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return a;
	}

	/**
	 * 分页条件查询请假记录
	 */
	public List<Attend> getLeavelAttendByTerm(String dep_name, int year, int month, int page, int rows) {
		// TODO Auto-generated method stub
		List<Attend> attends = new ArrayList<Attend>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ems_attend.uid,ems_attend.uname,");
		sql.append("ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '请假'");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			pst.setInt(4, (page-1)*rows);
			pst.setInt(5, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Attend attend = new Attend();
				attend.setUid(rs.getInt("uid"));
				attend.setUname(rs.getString("uname"));
				attend.setTime(rs.getString("time"));
				attend.setDate(rs.getString("date"));
				attend.setRemark(rs.getString("remark"));
				attends.add(attend);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return attends;
	}

	/**
	 * 获得条件查询出差记录总数
	 */
	public int getBtravelCountByTerm(String dep_name, int year, int month) {
		// TODO Auto-generated method stub
		int a = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(attend.uid) count");
		sql.append(" FROM (");
		sql.append(" SELECT ems_attend.uid,ems_attend.uname,");
		sql.append("ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '出差') AS attend");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				a = rs.getInt("count");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return a;
	}

	/**
	 * 分页条件查询出差记录总数
	 */
	public List<Attend> getBtravelAttendByTerm(String dep_name, int year, int month, int page, int rows) {
		// TODO Auto-generated method stub
		List<Attend> attends = new ArrayList<Attend>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ems_attend.uid,ems_attend.uname,");
		sql.append("ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_dept");
		sql.append(" ON ems_dept.dep_id = ems_user.dep_id");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_dept.dep_name = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '出差'");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dep_name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			pst.setInt(4, (page-1)*rows);
			pst.setInt(5, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Attend attend = new Attend();
				attend.setUid(rs.getInt("uid"));
				attend.setUname(rs.getString("uname"));
				attend.setTime(rs.getString("time"));
				attend.setDate(rs.getString("date"));
				attend.setRemark(rs.getString("remark"));
				attends.add(attend);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return attends;
	}


	public int getOnlyOvertimeCount(String uname, int year, int month) {
		// TODO Auto-generated method stub
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(ems_attend.uid) ucount");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_user.uname = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '加班'");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				flag = rs.getInt("ucount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return flag;
	}


	public int getOnlyLeavelCount(String uname, int year, int month) {
		// TODO Auto-generated method stub
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(ems_attend.uid) ucount");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_user.uname = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '请假'");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				flag = rs.getInt("ucount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return flag;
	}


	public int getOnlyBtravelCount(String uname, int year, int month) {
		// TODO Auto-generated method stub
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(ems_attend.uid) ucount");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_user.uname = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '出差'");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				flag = rs.getInt("ucount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return flag;
	}


	public List<Attend> getOnlyOvertime(String uname, int year, int month, int page, int rows) {
		// TODO Auto-generated method stub
		List<Attend> attends = new ArrayList<Attend>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ems_attend.uid,ems_attend.uname,");
		sql.append("ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_user.uname = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '加班'");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			pst.setInt(2, year);
			pst.setInt(3, month);
			pst.setInt(4, (page-1)*rows);
			pst.setInt(5, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Attend attend = new Attend();
				attend.setUid(rs.getInt("uid"));
				attend.setUname(rs.getString("uname"));
				attend.setTime(rs.getString("time"));
				attend.setDate(rs.getString("date"));
				attend.setRemark(rs.getString("remark"));
				attends.add(attend);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return attends;
	}


	public List<Attend> getOnlyLeavel(String uname, int year, int month, int page, int rows) {
		// TODO Auto-generated method stub
		List<Attend> attends = new ArrayList<Attend>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ems_attend.uid,ems_attend.uname,");
		sql.append("ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_user.uname = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '请假'");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			pst.setInt(2, year);
			pst.setInt(3, month);
			pst.setInt(4, (page-1)*rows);
			pst.setInt(5, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Attend attend = new Attend();
				attend.setUid(rs.getInt("uid"));
				attend.setUname(rs.getString("uname"));
				attend.setTime(rs.getString("time"));
				attend.setDate(rs.getString("date"));
				attend.setRemark(rs.getString("remark"));
				attends.add(attend);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return attends;
	}


	public List<Attend> getOnlyBtravel(String uname, int year, int month, int page, int rows) {
		// TODO Auto-generated method stub
		List<Attend> attends = new ArrayList<Attend>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ems_attend.uid,ems_attend.uname,");
		sql.append("ems_attend.time,ems_attend.date,ems_attend.remark");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_attend");
		sql.append(" ON ems_attend.uid = ems_user.uid");
		sql.append(" WHERE ems_user.uname = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND ems_attend.state_zxk = '出差'");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			pst.setInt(2, year);
			pst.setInt(3, month);
			pst.setInt(4, (page-1)*rows);
			pst.setInt(5, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Attend attend = new Attend();
				attend.setUid(rs.getInt("uid"));
				attend.setUname(rs.getString("uname"));
				attend.setTime(rs.getString("time"));
				attend.setDate(rs.getString("date"));
				attend.setRemark(rs.getString("remark"));
				attends.add(attend);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return attends;
	}
}
